package com.qyb.util;

import com.ecmp.util.JsonUtils;
import com.qyb.annotation.ExportName;
import jodd.datetime.JDateTime;
import jodd.typeconverter.Convert;
import net.sf.json.JSONArray;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.WorkbookUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;

/**
 * <p>
 * 实现功能：excel导入工具类
 * <p/>
 *
 * @author 秦有宝
 * @version 1.0.00
 */
public class ExcelUtils {

    /**
     *
     * @param file 文件
     * @param keys 字段名称数组，如  ["id", "name", ... ]
     * @param clazz item类
     * @return 导入结果Json
     */
    public static String ReadExcel(final MultipartFile file, String[] keys, Class clazz){
        ImportResult importResult = new ImportResult();
        List<Object> items = new ArrayList<>();
//        if (!file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".")+1).equals( "xls"))
        if (!file.getOriginalFilename().endsWith(".xls"))
        {
            importResult.setOperateStatus(new OperateStatus(false,"选择文件不是xls格式的文件，请重新上传文件！"));
            return JsonUtils.toJson(importResult);
        }
        InputStream is = null;
        try {
            is = file.getInputStream();
            if (is == null)
            {
                importResult.setOperateStatus(new OperateStatus(false,"未找到文件，请重新上传文件！"));
                return JsonUtils.toJson(importResult);
            }
            //解析文件
            Workbook wookbook = null;
            if(file.getOriginalFilename().endsWith(".xls")) {
                wookbook = new HSSFWorkbook(is);
            } else if(file.getOriginalFilename().endsWith(".xlsx")) {
                wookbook = new XSSFWorkbook(is);
            }
//            int sheetNumbers = wookbook.getNumberOfSheets();
            // 获取第一个工作表信息
            Sheet sheet = wookbook.getSheetAt(0);
            //获得数据的总行数
            int totalRowNum = sheet.getLastRowNum();
            // 获得表头
            Row rowHead = sheet.getRow(0);
            // 获得表头总列数
            int cols = rowHead.getPhysicalNumberOfCells();
            short s = rowHead.getLastCellNum();
            // 传入的key数组长度与表头长度不一致
            if(keys.length != cols) {
                importResult.setOperateStatus(new OperateStatus(false,"列数不对！"));
                return JsonUtils.toJson(importResult);
            }

            Row row;
            Cell cell;
            // 遍历所有行
            for (int i = 1; i <= totalRowNum; i++) {
                // 清空数据，避免遍历时读取上一次遍历数据
                row = sheet.getRow(i);
                // 该行为null
                if(null == row) {
                    continue;
                }
                Object item;
                try {
                    item = clazz.newInstance();
                    // 遍历该行所有列
                    for (short j = 0; j < cols; j++) {
                        cell = row.getCell(j);
                        // 根据poi返回的类型，做相应的get处理
                        String value = getCellValue(cell);

                        Field field = clazz.getDeclaredField(keys[j]);
//                        System.out.println(field);
                        //暴力反射，解除私有限定
                        field.setAccessible(true);
                        field.set(item, value);
                    }
                    /*Field field = clazz.getDeclaredField("id");
                    field.setAccessible(true);
                    field.set(item, UUID.randomUUID().toString());*/
                    items.add(item);
                } catch (Exception e) {
                    e.printStackTrace();
                    importResult.setOperateStatus(new OperateStatus(true,"行项目实例错误！"));
                    return JsonUtils.toJson(importResult);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (Objects.nonNull(is)) {
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }


        importResult.setItems(items);
        importResult.setOperateStatus(new OperateStatus(true,"导入成功！"));
        return JsonUtils.toJson(importResult);
    }

    /**
     * 根据Excel列类型获取列的值
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        if (cell == null){
            return "";
        }
        switch (cell.getCellTypeEnum()) {
            case STRING:
                return cell.getRichStringCellValue().getString();// cell.getStringCellValue();
            case NUMERIC:
                // if(cell.getCellStyle().getDataFormat() > 0)  {
                if (DateUtil.isCellDateFormatted(cell)) {
                    return DateUtils.formatDate(cell.getDateCellValue());
                } else {
                    return cell.getNumericCellValue()+"";
                }
            case BOOLEAN:
                return cell.getBooleanCellValue()+"";
            case FORMULA:
/*                try
                {
                    HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    e.EvaluateInCell(cell);
                    return cell.ToString();
                }
                catch
            {
                return cell.NumericCellValue.ToString();
            }*/
                return cell.getCellFormula().toString();
            case BLANK:
                return null;
            default:
                return "";
//                return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
        }
    }

    /**
     *
     * @param jsonData 需要导出的json数据
     * @param clazz 导出的实体类
     * @return excel
     */
    public static HSSFWorkbook generateExportWorkbook(String jsonData, Class clazz, Boolean isExport){
        Field[] fieldArray = clazz.getDeclaredFields();
        List<String> labelNames = new ArrayList<>();
        List<String> fields = new ArrayList<>();
        List<String> fieldTypes = new ArrayList<>();
        for(Field f : fieldArray){
            String name = f.getName();
            String typeName = f.getType().getTypeName();
            String exportName;
            ExportName entityComment = f.getAnnotation(ExportName.class);
            if (entityComment != null) {
                exportName = entityComment.value();
            } else {
                exportName = f.getName();
            }

            labelNames.add(exportName);
            fields.add(name);
            fieldTypes.add(typeName);

   /*         System.out.print(typeName + " ");
            System.out.print(name+ " ");
            System.out.print(exportName+ " ");
            System.out.println();*/
        }

        HSSFWorkbook wb = new HSSFWorkbook();
        Map<String, CellStyle> styles = createStyles(wb);
        String sheetName = WorkbookUtil.createSafeSheetName("表1");
        HSSFSheet sheet = wb.createSheet(sheetName);
        sheet.setDisplayGridlines(false);
        if(!isExport){
            for (int i = 0; i < fieldTypes.size(); i++) {
                if ("java.util.Date".equals(fieldTypes.get(i))) {        //Date Calendar
                    sheet.setDefaultColumnStyle(i, styles.get("dateCellStyle"));
                    //cell.setCellStyle(styles.get("dateCellStyle"));
                }else{
                    sheet.setDefaultColumnStyle(i, styles.get("dataCellStyle"));
                }
            }
        }

        //设置列宽: 第一个参数代表列id(从0开始),第2个参数代表宽度值  参考 ："2017-06-01"的宽度为2500
        sheet.setColumnWidth(0, 3766);
        sheet.setColumnWidth(1, 2500);
        sheet.setColumnWidth(2, 3766);
        sheet.setColumnWidth(3, 3766);
        sheet.setColumnWidth(4, 3766);

        Row labelRow = sheet.createRow((short) 0);
        for (int i = 0; i < labelNames.size(); i++) {
            Cell cell = labelRow.createCell(i);
            cell.setCellValue(labelNames.get(i));
            cell.setCellStyle(styles.get("labelCellStyle"));
        }
        JSONArray jsonArray = JSONArray.fromObject(jsonData);
        for (int i = 0; i < jsonArray.size(); i++) {
            Row dataRow = sheet.createRow((short) (1+i));
            for (int j = 0; j < fields.size(); j++) {
                Cell cell = dataRow.createCell(j);
                cell.setCellStyle(styles.get("dataCellStyle"));
                String fieldValue = jsonArray.getJSONObject(i).getString(fields.get(j));
                String fieldType = fieldTypes.get(j);

                if (fieldValue != null) {
                    try {
                        if ("java.lang.Byte".equals(fieldType)) {
                            if (fieldValue.toString().length() > 0) {
                                cell.setCellValue(Convert.toByte(fieldValue));
                            }
                        } else if ("java.lang.Short".equals(fieldType)) {
                            if (fieldValue.toString().length() > 0) {
                                cell.setCellValue(Convert.toShort(fieldValue));
                            }
                        } else if ("java.lang.Integer".equals(fieldType)) {
                            if (fieldValue.toString().length() > 0) {
                                cell.setCellValue(Convert.toInteger(fieldValue));
                            }
                        } else if ("java.lang.Long".equals(fieldType)) {
                            if (fieldValue.toString().length() > 0) {
                                cell.setCellValue(Convert.toLong(fieldValue));
                            }
                        } else if ("java.lang.Float".equals(fieldType)) {
                            if (fieldValue.toString().length() > 0) {
                                cell.setCellValue(Convert.toFloat(fieldValue));
                            }
                        } else if ("java.lang.Double".equals(fieldType)) {         //double
                            if (fieldValue.toString().length() > 0) {
                                cell.setCellValue(Convert.toDouble(fieldValue));
                            }
                        } else if ("java.math.BigDecimal".equals(fieldType)) {
                            if (fieldValue.toString().length() > 0) {
//                                cell.setCellValue(Convert.toBigDecimal(fieldValue));
                                cell.setCellValue(fieldValue);
                            }
                        } else if ("java.lang.Boolean".equals(fieldType)) {         //boolean
                            if (fieldValue.toString().length() > 0) {
                                cell.setCellValue(Convert.toBoolean(fieldValue));
                            }
                        } else if ("java.lang.String".equals(fieldType)) {      //RichTextString  String
                            cell.setCellValue(Convert.toString(fieldValue));
                        } else if ("java.util.Date".equals(fieldType)) {        //Date Calendar
                            if (fieldValue.toString().length() > 0) {
                                cell.setCellValue(DateUtils.formatDate(Convert.toDate(fieldValue)));
                                cell.setCellStyle(styles.get("dateCellStyle"));
                            }
                        } else if ("java.sql.Date".equals(fieldType)) {
                            if (fieldValue.toString().length() > 0) {
                                JDateTime jDateTime = new JDateTime(fieldValue.toString());
                                cell.setCellValue(jDateTime.convertToSqlDate());
                            }
                        } else if ("java.sql.Timestamp".equals(fieldType)) {
                            if (fieldValue.toString().length() > 0) {
                                JDateTime jDateTime = new JDateTime(fieldValue.toString());
                                cell.setCellValue(jDateTime.convertToSqlTimestamp());
                            }
                        } else {
                            cell.setCellValue(fieldValue);
                        }
                    } catch (Exception e) {
                        throw new RuntimeException(e.getMessage(), e);
                    }
                }
//              cell.setCellValue(fieldValue);
            }
        }
        return wb;
    }

    private static Map<String, CellStyle> createStyles(HSSFWorkbook wb){
        CreationHelper createHelper = wb.getCreationHelper();
        Map<String, CellStyle> styles = new HashMap<>();
        CellStyle dataCellStyle = wb.createCellStyle();
        dataCellStyle.setAlignment(HorizontalAlignment.CENTER);
        dataCellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
        HSSFFont font1  = wb.createFont();
        font1.setFontName("宋体");
        font1.setFontHeightInPoints((short) 9);
//        font.setColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        dataCellStyle.setFont(font1);
        dataCellStyle.setWrapText(true);
        dataCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dataCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dataCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dataCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dataCellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        dataCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        dataCellStyle.setBorderTop(BorderStyle.THIN);//上边框
        dataCellStyle.setBorderRight(BorderStyle.THIN);//右边框

        styles.put("dataCellStyle", dataCellStyle);

        //标题样式
        CellStyle labelCellStyle = wb.createCellStyle();
        labelCellStyle.setLocked(true);
        labelCellStyle.setShrinkToFit(false);
        //居中
        labelCellStyle.setAlignment(HorizontalAlignment.CENTER);
        labelCellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
        //字体 字号 粗体 颜色
        HSSFFont font  = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 9);
        font.setBold(true);
        font.setColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        labelCellStyle.setFont(font);
        //单元格内容自动换行
        labelCellStyle.setWrapText(true);
        //边框
        labelCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        labelCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        labelCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        labelCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        labelCellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        labelCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        labelCellStyle.setBorderTop(BorderStyle.THIN);//上边框
        labelCellStyle.setBorderRight(BorderStyle.THIN);//右边框
        labelCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        labelCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        styles.put("labelCellStyle", labelCellStyle);

        CellStyle dateCellStyle = wb.createCellStyle();
        dateCellStyle.setAlignment(HorizontalAlignment.CENTER);
        dateCellStyle.setVerticalAlignment( VerticalAlignment.CENTER);
        HSSFFont font2  = wb.createFont();
        font2.setFontName("宋体");
        font2.setFontHeightInPoints((short) 9);
//        font.setColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex());
        dateCellStyle.setFont(font2);
        dateCellStyle.setWrapText(true);
        dateCellStyle.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dateCellStyle.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dateCellStyle.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dateCellStyle.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        dateCellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        dateCellStyle.setBorderLeft(BorderStyle.THIN);//左边框
        dateCellStyle.setBorderTop(BorderStyle.THIN);//上边框
        dateCellStyle.setBorderRight(BorderStyle.THIN);//右边框
//        dataCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));
        dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("yyyy-MM-dd"));
        styles.put("dateCellStyle", dateCellStyle);
        return styles;
    }
}
